package com.ttctracker.util;

import java.net.URL;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;

import com.ttctracker.classes.Direction;
import com.ttctracker.classes.Route;
import com.ttctracker.classes.StopItem;
import com.ttctracker.splashscreen.SplashScreenActivity.StartupTask;
import com.ttctracker.xmlparser.SAXParserHelper;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteStatement;
import android.location.Location;
import android.os.AsyncTask;
import android.util.Log;

public class SqlAdapter {

	private static final String ROUTE_TABLE = "route";
	private static final String DIRECTION_TABLE = "direction";
	private static final String STOPS_TABLE = "stops";
	private static final String DIR_STOPS_TABLE = "dir_stops";
	private static final String USER_STOPS_TABLE = "user_stops";
	private static final String ROUTE_STOPS_URL = "http://webservices.nextbus.com/service/publicXMLFeed?terse&command=routeConfig&a=ttc&r=";
	private SqlOpenHelper openHelper;
	private SQLiteDatabase database;
	private Context context;
	private static SQLiteStatement insertDirectionStatement;
	private static SQLiteStatement insertRouteStatement;
	private static SQLiteStatement insertStopStatement;
	private static SQLiteStatement insertUserStopStatement;

	/**
	 * 
	 */
	public SqlAdapter(Context context) {
		this.context = context;
	}

	public boolean openDB() {
		try {
			openHelper = new SqlOpenHelper(context);
			database = openHelper.getWritableDatabase();
			return true;
		} catch (Exception e) {
			e.printStackTrace();
		}

		return false;
	}

	public void close() {
		if (database != null && database.isOpen()) {
			openHelper.close();
		}
	}

	public boolean isOpen() {
		if (database != null) {
			return database.isOpen();
		}
		return false;
	}

	/**
	 * This method should be called only once.
	 * 
	 * @param routes
	 * @return
	 */
	public long insert_route(ArrayList<Route> routes) {
		SQLiteStatement insertRouteStatement = database
				.compileStatement("INSERT INTO route(route_tag,route_title) VALUES(?,?)");
		long lastInsertedID = -1;
		for (Route route : routes) {
			insertRouteStatement.bindString(0, route.get_tag());
			insertRouteStatement.bindString(1, route.get_title());
			lastInsertedID = insertRouteStatement.executeInsert();
		}
		insertRouteStatement.close();
		return lastInsertedID;
	}

	/**
	 * This method should be called only once.
	 * 
	 * @param routes
	 * @return
	 */
	public long insert_route(HashMap<String, String> routes,StartupTask task) {

		insertRouteStatement = database
				.compileStatement("INSERT INTO route(route_tag,route_title) VALUES(?,?)");
		insertDirectionStatement = database
				.compileStatement("INSERT INTO "
						+ DIRECTION_TABLE
						+ "(dir_tag,dir_title,dir_name,route_tag,dir_stops) VALUES(?,?,?,?,?)");
		insertStopStatement = database
				.compileStatement("INSERT INTO "
						+ STOPS_TABLE
						+ "(stop_tag,stop_title,stop_lat,stop_lon,stop_id,route_tag) VALUES(?,?,?,?,?,?)");

		RouteStopsParseHandler handler = new RouteStopsParseHandler();
		final SAXParserHelper saxParserHelper = new SAXParserHelper(
				RouteStopsParseHandler.class);

		long lastInsertedID = -1;
		String[] routeTags = routes.keySet().toArray(new String[routes.size()]);

		int length = routeTags.length;
		Arrays.sort(routeTags);

		Route currentRoute = null;

		long start = System.currentTimeMillis();

		database.beginTransaction();

		try {

			saxParserHelper.setHandler(handler);
			saxParserHelper.setup_parser();

			for (int i = 0; i < length; i++) {
				saxParserHelper.setInputStream(new URL(ROUTE_STOPS_URL
						+ routeTags[i]).openStream());

				saxParserHelper.parseXml();

				currentRoute = handler.getSearchRoute();
				String route_tag = currentRoute.get_tag();
				insertRouteStatement.bindString(1, currentRoute.get_tag());
				insertRouteStatement.bindString(2, currentRoute.get_title());
				lastInsertedID = insertRouteStatement.executeInsert();

				insertAllStops(
						currentRoute
								.get_stopItems()
								.values()
								.toArray(
										new StopItem[currentRoute
												.get_stopItems().size()]),
						route_tag);
				insertDirections(currentRoute.get_directions(), route_tag);
				task.updateProgress(i+1);
			}
			database.setTransactionSuccessful();

		} catch (Exception e) {

			e.printStackTrace();
		} finally {
			database.endTransaction();
		}
		long duration = System.currentTimeMillis() - start;
		Log.i("AndroidNews", "Parser duration=" + duration);

		insertStopStatement.close();
		insertDirectionStatement.close();
		insertRouteStatement.close();
		return lastInsertedID;
	}

	/**
	 * Insert a single stop item
	 * 
	 * @param stopItem
	 *            -the given stop item
	 * @param route_tag
	 *            -the associated route tag
	 * @return -the inserted row id
	 */
	@SuppressWarnings("unused")
	private long insertStop(StopItem stopItem, String route_tag) {
		long lastInsertedID = -1;

		insertStopStatement.bindString(1, stopItem.get_tag());
		insertStopStatement.bindString(2, stopItem.get_title());
		insertStopStatement.bindDouble(3, stopItem.get_lat());
		insertStopStatement.bindDouble(4, stopItem.get_lon());
		insertStopStatement.bindString(5, stopItem.get_stopID() + "");
		insertStopStatement.bindString(6, route_tag);

		lastInsertedID = insertStopStatement.executeInsert();
		// insertStopStatement.close();
		return lastInsertedID;
	}

	/**
	 * Insert a list of stop items.
	 * 
	 * @param stopItems
	 *            -the list of stops
	 * @param route_tag
	 *            -the associated route tag
	 * @return -the last inserted id
	 */
	private long insertAllStops(StopItem[] stopItems, String route_tag) {

		long lastInsertedID = -1;

		for (StopItem stopItem : stopItems) {
			insertStopStatement.bindString(1, stopItem.get_tag());
			insertStopStatement.bindString(2, stopItem.get_title());
			insertStopStatement.bindDouble(3, stopItem.get_lat());
			insertStopStatement.bindDouble(4, stopItem.get_lon());
			insertStopStatement.bindString(5, stopItem.get_stopID() + "");
			insertStopStatement.bindString(6, route_tag);

			lastInsertedID = insertStopStatement.executeInsert();
		}
		// insertStopStatement.close();
		return lastInsertedID;
	}

	/**
	 * Insert all the route directions and link each direction to its stops.
	 * 
	 * @param directions
	 *            -the list of directions
	 * @param route_tag
	 *            -the associated route tag
	 * @return -the last inserted row id
	 */
	private long insertDirections(ArrayList<Direction> directions,
			String route_tag) {
		// SQLiteStatement
		// insertDirStopStatement=database.compileStatement("INSERT INTO "+DIR_STOPS_TABLE+"(dir_tag,stop_tag,route_tag) VALUES(?,?,?)");
		long lastInsertedID = -1;
		ArrayList<String> stops;
		StringBuffer sBuffer = new StringBuffer("");
		for (Direction direction : directions) {
			// String dir_tag=direction.get_tag();
			insertDirectionStatement.bindString(1, direction.get_tag());
			insertDirectionStatement.bindString(2, direction.get_title());
			insertDirectionStatement.bindString(3, direction.get_name());
			insertDirectionStatement.bindString(4, route_tag);

			stops = direction.get_stops();
			// int size=stops.size();
			for (String stopString : stops) {
				sBuffer.append(stopString);
				sBuffer.append(",");
			}
			insertDirectionStatement.bindString(5,
					sBuffer.substring(0, sBuffer.length() - 1));
			lastInsertedID = insertDirectionStatement.executeInsert();
			sBuffer.delete(0, sBuffer.length());
		}
		// insertDirectionStatement.close();
		// insertDirStopStatement.close();
		return lastInsertedID;
	}
	
	public long insertUserStop(StopItem stopItem) {
		long lastInsertedID = -1;
		//Log.i("route_stop", stopItem.get_routeTag()+" "+stopItem.get_tag());
		if (stopItem!=null) {
			//if (insertUserStopStatement==null) {
				insertUserStopStatement=database.compileStatement("INSERT INTO "+USER_STOPS_TABLE+"(stop_tag,s_id) VALUES(?,?)");
			//}
				try {
					long s_id=get_S_ID(stopItem.get_routeTag(), stopItem.get_tag());
					
					if (!isUserStopExist(s_id)) {
						database.beginTransaction();
						insertUserStopStatement.bindString(1, stopItem.get_tag());
						insertUserStopStatement.bindLong(2,s_id );
						lastInsertedID=insertUserStopStatement.executeInsert();
						insertUserStopStatement.close();
						database.setTransactionSuccessful();
						database.endTransaction();
					}else {
						lastInsertedID=-99;
					}
					
				} catch (Exception e) {
					e.printStackTrace();
				}
		}
		return lastInsertedID;
	}
	
	public boolean isUserStopExist(long s_id){
		boolean exist=false;
		Cursor cs = database.rawQuery("SELECT id FROM "+USER_STOPS_TABLE+" WHERE s_id="+s_id, null);
		if (cs!=null) {
			if (cs.moveToNext()) {
				exist=true;
			}
			cs.close();
		}
		return exist;
	}
	
	private long get_S_ID(String route_tag,String stop_tag){
		long s_id=-1;
		SQLiteStatement query_S_ID_Statement=database.compileStatement("SELECT id FROM "+STOPS_TABLE+" WHERE route_tag=? AND stop_tag=?");
		query_S_ID_Statement.bindString(1, route_tag);
		query_S_ID_Statement.bindString(2, stop_tag);
		s_id= query_S_ID_Statement.simpleQueryForLong();
		query_S_ID_Statement.close();
		return s_id;
	}
	

	public HashMap<String, String> selectRouteTags() {
		HashMap<String, String> routeTagsHashMap = new HashMap<String, String>();
		Cursor cs = database.rawQuery("SELECT route_tag,route_title FROM "
				+ ROUTE_TABLE+" WHERE route_tag not like '3__'", null);
		if (cs != null) {
			while (cs.moveToNext()) {
				routeTagsHashMap.put(cs.getString(0),
						cs.getString(1).replace('-', ' '));
			}
			cs.close();
		}
		return routeTagsHashMap;
	}

	public boolean gotRouteTableExist() {
		Cursor cs = database.rawQuery("SELECT * FROM " + ROUTE_TABLE
				+ " LIMIT 1", null);
		if (cs != null) {
			if (cs.moveToFirst()) {
				cs.close();
				return true;
			} else {
				cs.close();
			}
		}
		return false;
	}

	public Route selectRoute(String route_tag , boolean isOnMap) {
		Cursor cs = database.rawQuery("SELECT route_tag, route_title FROM "
				+ ROUTE_TABLE + " WHERE route_tag='" + route_tag + "' LIMIT 1",
				null);
		Route selectedRoute = null;
		if (cs != null) {
			if (cs.moveToNext()) {
				selectedRoute = new Route(cs.getString(0), cs.getString(1));

				ArrayList<Direction> directions = cursorToDirections(database
						.rawQuery(
								"SELECT dir_tag, dir_title, dir_name, dir_stops FROM "
										+ DIRECTION_TABLE
										+ " WHERE route_tag='" + route_tag
										+ "'", null));
				if (directions != null) {
					selectedRoute.set_directions(directions);
				}
				HashMap<String, StopItem> stops =null;
				if (!isOnMap) {
					stops= cursorToStops(database
							.rawQuery(
									"SELECT stop_tag,stop_title,stop_lat,stop_lon,stop_id, route_tag FROM "
											+ STOPS_TABLE + " WHERE route_tag='"
											+ route_tag + "'", null));
				}else {
					stops= cursorToStops(database
							.rawQuery(
									"SELECT stop_tag,stop_title,stop_lat,stop_lon,stop_id, route_tag FROM "
											+ STOPS_TABLE + " WHERE not stop_id ='' and route_tag='"
											+ route_tag + "'", null));
				}
				
				if (stops != null) {
					selectedRoute.set_stopItems(stops);
				}
			}
			cs.close();
		}

		return selectedRoute;
	}

	/*
	 * public Route selectAllRoute() { Cursor cs =
	 * database.rawQuery("SELECT route_tag, route_title FROM " + ROUTE_TABLE ,
	 * null); Route selectedRoute = null; if (cs != null) { if (cs.moveToNext())
	 * { selectedRoute = new Route(cs.getString(0), cs.getString(1));
	 * 
	 * ArrayList<Direction> directions = cursorToDirections(database .rawQuery(
	 * "SELECT dir_tag, dir_title, dir_name, dir_stops FROM " + DIRECTION_TABLE
	 * + " WHERE route_tag='" + route_tag + "'", null)); if (directions != null)
	 * { selectedRoute.set_directions(directions); }
	 * 
	 * HashMap<String, StopItem> stops = cursorToStops(database .rawQuery(
	 * "SELECT stop_tag,stop_title,stop_lat,stop_lon,stop_id FROM " +
	 * STOPS_TABLE + " WHERE route_tag='" + route_tag + "'", null)); if (stops
	 * != null) { selectedRoute.set_stopItems(stops); } } cs.close(); }
	 * 
	 * return selectedRoute; }
	 */

	/*private StopItem[] selectAllStopItems() {
		HashMap<String, StopItem> stops = cursorToStops(database.rawQuery(
				"SELECT stop_tag,stop_title,stop_lat,stop_lon,stop_id FROM "
						+ STOPS_TABLE, null));
		if (stops != null) {
			return stops.values().toArray(new StopItem[stops.size()]);
		} else {
			return null;
		}
	}*/

	private ArrayList<Direction> cursorToDirections(Cursor cs) {
		ArrayList<Direction> directions = new ArrayList<Direction>();
		if (cs != null) {
			Direction tempDir = null;
			while (cs.moveToNext()) {
				tempDir = new Direction();
				tempDir.set_tag(cs.getString(0));
				tempDir.set_title(cs.getString(1));
				tempDir.set_name(cs.getString(2));
				String dir_stopsString = cs.getString(3);
				if (dir_stopsString != null) {
					String[] dir_stops = dir_stopsString.split(",");
					if (dir_stops != null) {
						tempDir.set_stops(new ArrayList<String>(Arrays
								.asList(dir_stops)));
					}
				}
				directions.add(tempDir);
			}
			cs.close();
			return directions;
		}
		return null;

	}

	private HashMap<String, StopItem> cursorToStops(Cursor cs) {
		HashMap<String, StopItem> stops = new HashMap<String, StopItem>();
		if (cs != null) {
			StopItem tempStop = null;
			while (cs.moveToNext()) {
				tempStop = new StopItem();
				tempStop.set_tag(cs.getString(0));
				tempStop.set_title(cs.getString(1));
				tempStop.set_lat(cs.getDouble(2));
				tempStop.set_lon(cs.getDouble(3));
				tempStop.set_stopID(cs.getString(4));
				tempStop.set_routeTag(cs.getString(5));
				stops.put(tempStop.get_tag(), tempStop);
			}
			cs.close();
			return stops;
		}
		return null;
	}

	/*private String getRouteTagByStopTag(String stopTag) {
		Cursor cs = database.rawQuery("SELECT route_tag FROM " + STOPS_TABLE
				+ " WHERE stop_tag='" + stopTag + "' LIMIT 1", null);
		if (cs.moveToNext()) {
			return cs.getString(0);
		} else {
			return null;
		}
	}*/

	public ArrayList<Route> getNearByRoutes(double userLat,double userLon) {
		//final int max = 8;
		//int countNearByRoutes = 0;
		String currentNearByRouteTag = "";
		ArrayList<String> nearByRouteTags = new ArrayList<String>();
		ArrayList<Route> nearByRoutes=null;
		//StopItem[] stopItems = selectAllStopItems();
		/*double startLatitude = userLocation.getLatitudeE6() * -1e6;
		double startLongitude = userLocation.getLongitudeE6() * -1e6;*/
		float[] results = new float[1];
		//long start = System.currentTimeMillis();
		
		Cursor cs=database.rawQuery(
				"SELECT stop_lat,stop_lon,route_tag FROM "
						+ STOPS_TABLE +" WHERE route_tag not like '3__'", null);
		
		if (cs != null) {
			while(cs.moveToNext()) {
				//if (!cs.getString(2).startsWith("3")) {
						if (!currentNearByRouteTag.equals(cs.getString(2))) {
	
						Location.distanceBetween(userLat, userLon,
								cs.getDouble(0),cs.getDouble(1), results);
						if (results != null) {
							if (results[0] <= 1000) {
								currentNearByRouteTag =cs.getString(2);
								//countNearByRoutes++;
								nearByRouteTags.add(currentNearByRouteTag);
								//if (countNearByRoutes==max) {
									
									//break;
								//}
							}
						}
	
					}
				//}
				

			}
			cs.close();
			
			if (nearByRouteTags!=null) {
				if (nearByRouteTags.size()>0) {
					nearByRoutes = new ArrayList<Route>();
					for (String routeTag : nearByRouteTags) {
						nearByRoutes.add(selectRoute(routeTag,true));
					}
				}
			}
			//long duration = System.currentTimeMillis() - start;
			//Log.i("AndroidNews", "Parser duration=" + duration);
		}
		return nearByRoutes;
	}
}
